/*
 * hkh-project
 * leafteamwork.blogspot.com
 * Tool: Netbeans 6.7.1 - SQL Server 2005
 */
package vn.com.hkh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;
import vn.com.hkh.dto.BookDTO;
import vn.com.hkh.util.DatabaseUtil;

/**
 *
 * @author leaf
 */
public class BookDAO {

    // Log
    private static Logger logger = Logger.getLogger(PageDAO.class.getName());
    //C: Create
    private final String SQL_CREATE = "INSERT INTO hkh_booking_table (book_id, customer_fullname, customer_email, customer_phone, customer_address, arrive_date, departure_date, room_count, adults_count, children_count, other_request) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
    //R: Read
    private final String SQL_READ_BY_ID = "SELECT book_id, customer_fullname, customer_email, customer_phone, customer_address, arrive_date, departure_date, room_count, adults_count, children_count, other_request FROM hkh_booking_table WHERE (book_id = ?)";
    private final String SQL_READ_BY_ALL = "SELECT book_id, customer_fullname, customer_email, customer_phone, customer_address, arrive_date, departure_date, room_count, adults_count, children_count, other_request FROM hkh_booking_table ORDER BY arrive_date DESC";
    //U: Update
    private final String SQL_UPDATE = "UPDATE hkh_booking_table SET customer_fullname =?, customer_email =?, customer_phone =?, customer_address =?, arrive_date =?, departure_date =?, room_count =?, adults_count =?, children_count =?, other_request =? WHERE (book_id = ?)";
    //D: Delete
    private final String SQL_DELETE_BY_ID = "DELETE FROM hkh_booking_table WHERE (book_id = ?)";

    /**
     * Create new Booking
     * @param _bookDTO
     * @return BookDTO
     */
    public BookDTO create(BookDTO _bookDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_CREATE);

            _ptmt.setString(1, _bookDTO.getId());
            _ptmt.setString(2, _bookDTO.getCustomerFullname());
            _ptmt.setString(3, _bookDTO.getCustomerEmail());
            _ptmt.setString(4, _bookDTO.getCustomerPhone());
            _ptmt.setString(5, _bookDTO.getCustomerAddress());
            _ptmt.setString(6, _bookDTO.getArriveDate());
            _ptmt.setString(7, _bookDTO.getDepartureDate());
            _ptmt.setInt(8, _bookDTO.getRoomCount());
            _ptmt.setInt(9, _bookDTO.getAdultsCount());
            _ptmt.setInt(10, _bookDTO.getChildrenCount());
            _ptmt.setString(11, _bookDTO.getOtherRequest());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _bookDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read Booking By Id
     * @param _id
     * @return BookDTO
     */
    public BookDTO readByID(String _id) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        BookDTO _bookDTO = null;
        try {

            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_READ_BY_ID);
            _ptmt.setString(1, _id);

            ResultSet _rs = _ptmt.executeQuery();
            if (_rs.next()) {
                _bookDTO = new BookDTO();
                _bookDTO.setId(_rs.getString(1));
                _bookDTO.setCustomerFullname(_rs.getString(2));
                _bookDTO.setCustomerEmail(_rs.getString(3));
                _bookDTO.setCustomerPhone(_rs.getString(4));
                _bookDTO.setCustomerAddress(_rs.getString(5));
                _bookDTO.setArriveDate(_rs.getString(6));
                _bookDTO.setDepartureDate(_rs.getString(7));
                _bookDTO.setRoomCount(_rs.getInt(8));
                _bookDTO.setAdultsCount(_rs.getInt(9));
                _bookDTO.setChildrenCount(_rs.getInt(10));
                _bookDTO.setOtherRequest(_rs.getString(11));
            }
            _rs.close();
            _ptmt.close();
            return _bookDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Read all Booking
     * @return Collection<BookDTO>
     */
    public Collection<BookDTO> readByAll() {
        Collection<BookDTO> _list = null;
        Connection _con = null;
        Statement _stmt = null;
        BookDTO _bookDTO = null;
        try {
            _con = DatabaseUtil.getConnection();
            _stmt = _con.createStatement();
            ResultSet _rs = _stmt.executeQuery(SQL_READ_BY_ALL);

            _list = new ArrayList<BookDTO>();
            while (_rs.next()) {
                _bookDTO = new BookDTO();
                _bookDTO.setId(_rs.getString(1));
                _bookDTO.setCustomerFullname(_rs.getString(2));
                _bookDTO.setCustomerEmail(_rs.getString(3));
                _bookDTO.setCustomerPhone(_rs.getString(4));
                _bookDTO.setCustomerAddress(_rs.getString(5));
                _bookDTO.setArriveDate(_rs.getString(6));
                _bookDTO.setDepartureDate(_rs.getString(7));
                _bookDTO.setRoomCount(_rs.getInt(8));
                _bookDTO.setAdultsCount(_rs.getInt(9));
                _bookDTO.setChildrenCount(_rs.getInt(10));
                _bookDTO.setOtherRequest(_rs.getString(11));

                _list.add(_bookDTO);
            }
            _rs.close();
            _stmt.close();
            return _list;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Update Booking
     * @param _bookDTO
     * @return BookDTO
     */
    public BookDTO update(BookDTO _bookDTO) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_UPDATE);

            _ptmt.setString(1, _bookDTO.getCustomerFullname());
            _ptmt.setString(2, _bookDTO.getCustomerEmail());
            _ptmt.setString(3, _bookDTO.getCustomerPhone());
            _ptmt.setString(4, _bookDTO.getCustomerAddress());
            _ptmt.setString(5, _bookDTO.getArriveDate());
            _ptmt.setString(6, _bookDTO.getDepartureDate());
            _ptmt.setInt(7, _bookDTO.getRoomCount());
            _ptmt.setInt(8, _bookDTO.getAdultsCount());
            _ptmt.setInt(9, _bookDTO.getChildrenCount());
            _ptmt.setString(10, _bookDTO.getOtherRequest());
            _ptmt.setString(11, _bookDTO.getId());

            _ptmt.executeUpdate();
            _ptmt.close();
            return _bookDTO;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }

    /**
     * Delete Book
     * @param _bookID
     * @return boolean
     */
    public boolean deleteById(String _bookID) {
        Connection _con = null;
        PreparedStatement _ptmt = null;
        try {
            _con = DatabaseUtil.getConnection();
            _ptmt = _con.prepareStatement(SQL_DELETE_BY_ID);
            _ptmt.setString(1, _bookID);
            _ptmt.executeUpdate();

            _ptmt.close();
            return true;
        } catch (SQLException _ex) {
            logger.log(Level.SEVERE, null, _ex);
            return false;
        } finally {
            DatabaseUtil.closeConnection(_con);
        }
    }
}
